package net.lmc.realtime.traffic.mapper;


import net.lmc.realtime.traffic.bean.TrafficMonitoring;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface TransportstionMapper {

   @Select("select road_id,\n" +
           "       round(avgspeed,2) as avgspeed,\n" +
           "       congestion_level\n" +
           "       from (\n" +
           "select road_id,\n" +
           "       sum(avgspeed)/sum(severeCount) as avgspeed,\n" +
           "       case when avgspeed< 66 then '拥堵'\n" +
           "               else '畅通' end as congestion_level\n" +
           "from traffic_monitoring.dwd_congestion\n" +
           "group by road_id )\n" +
           "         where congestion_level='拥堵'\n" +
           "order by avgspeed  limit 10")
   public List<TrafficMonitoring> selectTop();
}
